<?php

/**
 * Library :: DAL (Data Access Layer) :: DAO[mysql]
 * 
 * The mysql specific DAO driver.
 *
 * Warlock is free software. This version may have been modified pursuant
 * to the GNU General Public License, and as distributed it includes or
 * is derivative of works licensed under the GNU General Public License or
 * other free or open source software licenses.
 *
 * @package    warlock
 * @author     David Cole <neophyte@sourcetutor.com>
 * @license    GNU/GPL v2
 */

// Object Dependencies
using('Engine.DAL.DAO');

class DAL_DAO_mysql extends DAL_DAO
{
    /**
     * Data Access Layer
     *
     * @var DAL
     * @access private
     */
    var $_DAL;

    /**
     * DAO Config
     *
     * @var array
     * @access private
     */
    var $_DAOConfig = array();

    /**
     * Mysql Connection Resource
     *
     * @var res
     * @access private
     */
    var $_Connection;

    /**#@+
     * Required Core Objects
     *
     * These are references to the core objects required by this
     * class.
     *
     * @access private
     * @var    object
     */
    var $_Error;
    var $_Config;
    var $_Profiler;
    /**#@-*/

    /**
     * Constructor
     *
     * Performs general object startup tasks such as getting instances to the required
     * core objects and establishing a connection to the mysql server
     *
     * @return DAL_DAO_mysql
     */
    function DAL_DAO_mysql()
    {
        // Get References to required objects
        $this->_Error    = &Vault::Get('Debug_Error');
        $this->_Profiler = &Vault::Get('Debug_Profiling');
        $this->_Config   = &Vault::Get('Config');
        $this->_DAL      = &Vault::Get('DAL');

        // Get DAO Config
        $this->_DAOConfig = $this->_Config->Get('Database');

        /*
         * Establish the mysql connection, if there are any failures then
         * class the system as unusable.
         */

        if (!($this->_Connection = mysql_connect($this->_DAOConfig['hostname'], $this->_DAOConfig['username'], $this->_DAOConfig['password'])))
            $this->_Error->Log('DAL:DAO[mysql] :: Connection Error \''.mysql_error().'\'', W_LOG_EMERG);

        if (!(mysql_select_db($this->_DAOConfig['database'], $this->_Connection)))
            $this->_Error->Log('DAL:DAO[mysql] :: Database Selection Error \''.mysql_error().'\'', W_LOG_EMERG);
    }

    /**
     * Select
     *
     * RDBMS specific implementation of the abstract
     * method Select.
     *
     * @param   string  $do       Data Object
     * @param   array   $where    Associative array of where conditions
     * @param   array   $order    Associative array of field orders
     * @param   int     $rows     Number of rows to retrieve
     * @param   int     $offset   Offset from which rows should be retrieved
     * @param   string  $mode     How returned rows should be formatted
     *
     * @return object             DAL_DAO_ResultSet|DAL_DataObjectRow false on failure
     * @access public
     */
    function Select($do, $where, $order, $rows, $offset, $mode)
    {
        $sql = 'SELECT '
               .$this->_QueryBuilder_Select($do)."\n"
               .'  FROM '.$this->_DAL->DataObjects[$do]->Table."\n"
               .$this->_QueryBuilder_Join($do)."\n"
               .$this->_QueryBuilder_Where($do, $where)."\n"
               .$this->_QueryBuilder_Order($do, $order)."\n"
               .$this->_QueryBuilder_Limit($rows, $offset);

        if (!($res = mysql_query($sql)))
        {
            $this->_Error->Log('DAL : DAO[mysql] :: MySql Error \''.mysql_error($this->_Connection).'\' on query \''.$sql.'\'', W_LOG_CRIT);
            return false;
        }
        else
        {
            $this->_Error->Log('DAL : DAO[mysql] :: Query \''.$sql.'\'', W_LOG_DEBUG);

            if (is_scalar($where) || $rows === 1)
            {
                $temp = new DAL_DAO_mysql_ResultSet($do, $res, $mode);
                return $temp->FetchRow();
            }
            else
                return new DAL_DAO_mysql_ResultSet($do, $res, $mode);
        }
    }

    /**
     * Insert
     *
     * RDBMS specific implementation of the abstract
     * method Insert.
     *
     * @param   string  $do    Data Object
     * @param   array   $data  Associative array of field->value pairs
     * @param   bool    $log   Whether to log the Data Object action or not
     *
     * @return int             ID of inserted record (0 of none available) false on failure
     * @access public
     */
    function Insert($do, $data = array())
    {
        $foreign_keys = array();
        
        foreach ($this->_DAL->DataObjects[$do]->Relationships as $related)
        {
            $temp               = array();
            $temp['_PROCESSED'] = true;
            
            foreach ($related['fields'] as $field => $alias)
                if (isset($data[$alias]))
                    $temp[$field] = $data[$alias];                    
                               
            if (count($temp) > 1)  
                $foreign_keys[array_search($this->_DAL->DataObjects[$related['do']]->PrimaryKey, $related['relate'])] = $this->_DAL->Insert($related['do'], $temp);            
        }
        
        $fields = array();
        $values = array();
        
        // Set defaults for any fields not specified
        foreach ($this->_DAL->DataObjects[$do]->Fields as $field => $attributes)
        {     	           	    
            if (!isset($data[$field]))
            {
                if (isset($foreign_keys[$field]))
                {
                    $fields[] = $field;
                    $values[] = $foreign_keys[$field];
                }
                else
                {                        
                    if (($ret = $this->_SetFieldDefault($attributes, 'INSERT')) !== false)
                    {
                        $values[] = $ret;
                        $fields[] = $field;
                    }
                }
            }
            else 
            {
                $fields[] = $field;
                $values[] = $data[$field];
            }
        }              
        
        $sql = 'INSERT INTO '.$this->_DAL->DataObjects[$do]->Table.' ('.implode(',', $fields).') VALUES ('.implode(',', $values).')';

        if (!($res = mysql_query($sql)))
        {
            $this->_Error->Log('DAL : DAO[mysql] :: MySql Error \''.mysql_error($this->_Connection).'\' on query \''.$sql.'\'', W_LOG_CRIT);
            return false;
        }
        else
        {
            $this->_Error->Log('DAL : DAO[mysql] :: Query \''.$sql.'\'', W_LOG_DEBUG);
            return mysql_insert_id($this->_Connection);
        }
    }

    function _SetFieldDefault($field, $type)
    {
        $ret = false;
        
        if ( ($type == 'INSERT' && isset($field['autoinsert']) && $field['autoinsert']) ||
             ($type == 'INSERT' && isset($field['autoupdate']) && $field['autoupdate']) ||
             ($type == 'UPDATE' && isset($field['autoupdate']) && $field['autoupdate']) )
        {
            switch ($field['type'])
            {
                case 'DATETIME':
                    $ret = 'NOW()';
                    break;
                    
                default:
                    $ret = false;                     
            }
        }
        
        return $ret;
    }
    
    /**
     * Update
     *
     * RDBS specific implementation of the abstract
     * method Insert.
     *
     * @param   string  $do     Data Object
     * @param   array   $data   Associative array of field->value pairs
     * @param   array   $where  Associative array of where conditions
     * @param   bool    $log    Whether to log the Data Object action or not
     *
     * @return int              Number of affected records or false or failure.
     * @access public
     */
    function Update($do, $data = array(), $where = array())
    {
        $updates = array();
        
        // Data Object Specific
        foreach ($this->_DAL->DataObjects[$do]->Fields as $field => $attributes)
        {
            if ($field != $this->_DAL->DataObjects[$do]->PrimaryKey)
            {
                if (isset($data[$field]))
                    $updates[] = $this->_DAL->DataObjects[$do]->Table.'.'.$field.' = '.$data[$field];
                else
                    if (($ret = $this->_SetFieldDefault($attributes, 'UPDATE')) !== false)
                        $updates[] = $this->_DAL->DataObjects[$do]->Table.'.'.$field.' = '.$ret;
            }
        }
        
        // Fields From Joined Data Objects
        foreach ($this->_DAL->DataObjects[$do]->Relationships as $related)
        {
            foreach ($this->_DAL->DataObjects[$related['do']]->Fields as $field => $attributes)            
            {               
                if ($field != $this->_DAL->DataObjects[$related['do']]->PrimaryKey)
                {
                    if (isset($related['fields'][$field]))
                    {
                        $alias = $related['fields'][$field];
                        
                        if (isset($data[$alias]))
                            $updates[] = $related['table'].'.'.$field.' = '.$data[$alias];
                        else
                            if (($ret = $this->_SetFieldDefault($attributes, 'UPDATE')) !== false)
                                $updates[] = $related['table'].'.'.$field.' = '.$ret;
                    }
                }                
            }
        }
        
        $sql =  'UPDATE '.$this->_DAL->DataObjects[$do]->Table."\n"
               .$this->_QueryBuilder_Join($do)."\n"
               .'  SET '.implode(', ', $updates)
               .'  '.$this->_QueryBuilder_Where($do, $where);

        if (!($res = mysql_query($sql)))
        {
            $this->_Error->Log('DAL : DAO[mysql] :: MySql Error \''.mysql_error($this->_Connection).'\' on query \''.$sql.'\'', W_LOG_CRIT);
            return false;
        }
        else
        {
            $this->_Error->Log('DAL : DAO[mysql] :: Query \''.$sql.'\'', W_LOG_DEBUG);
            return mysql_affected_rows($this->_Connection);
        }
    }

    /**
     * Delete
     *
     * RDBMS specific implementation of the abstract
     * method Delete.
     *
     * @param   string  $do     Data Object
     * @param   array   $where  Associative array of where conditions
     * @param   bool    $log    Whether to log the Data Object action or not
     *
     * @return int              Number of affected records or false on failure.
     * @access public
     */
    function Delete($do, $where = array())
    {
		$table = array($this->_DAL->DataObjects[$do]->Table); // Tables from which rows will be deleted
		$join  = array(); // Join statements
    			
        foreach ($this->_DAL->DataObjects[$do]->Relationships as $relation)
        {        	        	
        	if (isset($relation['type']) && ($relation['type'] == 'CASCADE'))    	
        	{
        		$table[] = $relation['table'];

                $temp  = array();

                foreach ($relation['relate'] as $parent_field => $child_key)
                    $temp[] = ((strpos($parent_field, '.'))?$parent_field:$this->_DAL->DataObjects[$do]->Table.'.'.$parent_field).' = '.$relation['table'].'.'.$child_key;

                $join[] = 'LEFT JOIN '.$relation['table'].' ON '.implode(' AND ', $temp);        		        		
        	}
        }
    	    	
        $sql =   'DELETE '."\n"
                .implode(','."\n", $table)."\n"
                .'  FROM '.$this->_DAL->DataObjects[$do]->Table."\n"
                .implode("\n", $join)." \n"
                .$this->_QueryBuilder_Where($do, $where);

        if (!($res = mysql_query($sql)))
        {
            $this->_Error->Log('DAL : DAO[mysql] :: MySql Error \''.mysql_error($this->_Connection).'\' on query \''.$sql.'\'', W_LOG_CRIT);
            return false;
        }
        else
        {
        	// Grab results	
            $this->_Error->Log('DAL : DAO[mysql] :: Query \''.$sql.'\'', W_LOG_DEBUG);
            return mysql_affected_rows($this->_Connection);
        }
    }

    /**
     * Escape String
     *
     * The RDBMS specific method of escaping a string, needed by the DAL
     * when validating fields and escaping certain conditions.
     *
     * @param string $value
     *
     * @return unknown
     * @access public
     */
    function EscapeString($value)
    {
        return mysql_real_escape_string($value, $this->_Connection);
    }

    /**
     * Query Builder - SELECT
     *
     * @param string $do   Data Object
     *
     * @return unknown
     * @access private
     */
    function _QueryBuilder_Select($do)
    {
        $select = array();

        // We want all the table fields
        $select[] = $this->_DAL->DataObjects[$do]->Table.'.*';

        // Fields specified within relationships
        foreach ($this->_DAL->DataObjects[$do]->Relationships as $relation)
            if (isset($relation['fields']) && is_array($relation['fields']))
                foreach ($relation['fields'] as $field => $alias)
                    $select[] = ((isset($relation['alias']))?$relation['alias']:$relation['table']).'.'.$field.' AS '.$alias;

        return implode(", \n", $select);
    }

    /**
     * Query Builder - JOIN
     *
     * @param string $do   Data Object
     *
     * @return string
     * @access private
     */
    function _QueryBuilder_Join($do)
    {
        $join = array();

        // We only join tables for which we need info, other relations are simply used for data integrity
        foreach ($this->_DAL->DataObjects[$do]->Relationships as $relation)
        {
            if (isset($relation['fields']) && is_array($relation['fields']) && isset($relation['relate']) && is_array($relation['fields']))
            {
                $temp  = array();
                $table = isset($relation['alias']) ? $relation['alias'] : $relation['table'];

                foreach ($relation['relate'] as $parent_field => $child_key)
                    $temp[] = ((strpos($parent_field, '.'))?$parent_field:$this->_DAL->DataObjects[$do]->Table.'.'.$parent_field).' = '.$table.'.'.$child_key;

                $join[] = 'LEFT JOIN '.$relation['table'].((isset($relation['alias']))?' AS '.$relation['alias'] :'').' ON '.implode(' AND ', $temp);
            }
        }

        return implode("\n", $join);
    }

    /**
     * Query Builder - WHERE
     *
     * @param string $do      Data Object
     * @param array  $where   Array of where conditions
     *
     * @return string
     * @access private
     */
    function _QueryBuilder_Where($do, $where)
    {
        if (count($where) > 0)
        {
            $table = $this->_DAL->DataObjects[$do]->Table;
            $pkey  = $this->_DAL->DataObjects[$do]->PrimaryKey;

            if (is_scalar($where))
                return 'WHERE '.$table.'.'.$pkey.' = \''.$this->EscapeString($where).'\'';

            $sql = array();

            foreach ($where as $field => $condition)
            {
                if (is_scalar($condition))
                     $sql[] = $table.'.'.$field.' = \''.$this->EscapeString($condition).'\'';
                else
                {
                    $co    = isset($condition[0]) ? $condition[0] : '=';
                    $value = isset($condition[1]) ? $condition[1] : '';
                    $type  = isset($condition[2]) ? $condition[2] : 'string';
                    $func  = isset($condition[3]) ? $condition[3] : false;

                    if (!$func)
                    {
                        switch ($type)
                        {
                            case 'field'  : $value = '`'.$this->EscapeString($value).'`'; break;
                            case 'int'    : if (!is_numeric($value)) $this->_Error->Log('DAL : DAO[mysql] :: Expecting an int got \''.$value.'\' instead.', W_LOG_EMERG); break;
                            case 'string' : $value = '\''.$this->EscapeString($value).'\''; break;
                        }

                        $sql[] = $table.'.'.$field.' '.$co.' '.$value;
                    }
                    else
                    {
                        switch (strtoupper($func))
                        {
                            case 'NOW'    : $sql[] = $table.'.'.$field.' '.$co.' NOW()'; break;
                        }
                    }
                }
            }

            return ' WHERE '.implode(' AND ', $sql);
        }
        else
            return '';
    }

    /**
     * Query Builder - ORDER
     *
     * @param string $do       Data Object
     * @param array  $orders   field => order associative array
     *
     * @return string
     * @access private
     */
    function _QueryBuilder_Order($do, $orders)
    {
        if (count($orders) > 0)
        {
            $order = array();

            foreach ($orders as $field => $sort)
                $order[] = $this->_DAL->DataObjects[$do]->Table.'.'.$field.' '.$sort;

            return 'ORDER BY '.implode(', ', $order);
        }
        else
            return '';
    }

    /**
     * Query Builder - LIMIT
     *
     * @param int $rows     Number of rows to be returned
     * @param int $offset   Offset to begin return rows from
     *
     * @return string
     * @access private
     */
    function _QueryBuilder_Limit($rows, $offset)
    {
        if ((!is_numeric($rows)) && (!is_numeric($offset)))
            return '';
        else
            return (($offset)?'LIMIT '.$offset.','.$rows:'LIMIT '.$rows);
    }
}

class DAL_DAO_mysql_ResultSet extends DAL_DAO_ResultSet
{
    /**
     * Data Access Layer
     *
     * @var DAL
     * @access private
     */
    var $_DAL;

    /**
     * Mysql Result Resource
     *
     * @var resource
     * @access private
     */
    var $_Resource;

    /**
     * Data Object
     *
     * The Data Object type to which the
     * results belong.
     *
     * @var string
     * @access private
     */
    var $_DataObject;

    /**
     * Result Mode
     *
     * Determines how results should be returned
     * by the result set, available options are :
     *
     * ASSOC    - Associate Array
     * ROW      - Numerical Array
     * DOBJECT  - Single Row Data Object
     *
     * @var string
     * @access private
     */
    var $_Mode;

    /**
     * Constructor
     *
     * @param string    $do    Data Object to which the results belong
     * @param resource  $res   MySql Result Resource
     * @param string    $mode  How results should be returned
     *
     * @return DAL_DAO_mysql_ResultSet
     */
    function DAL_DAO_mysql_ResultSet($do, $res, $mode)
    {
        $this->_DAL = &Vault::get('DAL');

        $this->_Resource   = $res;
        $this->_DataObject = $do;
        $this->_Mode       = $mode;
    }

    /**
     * Fetch Row
     *
     * Returns a single result from the result set
     * based on the return mode or false when theres
     * no more results left to return
     *
     * @return mixed
     * @access public
     */
    function FetchRow($mode = false)
    {
        $mode = ($mode)?$mode:$this->_Mode;
        
        switch ($mode)
        {
            case 'ASSOC':
                return mysql_fetch_assoc($this->_Resource);
                break;
            case 'ROW':
                return mysql_fetch_row($this->_Resource);
                break;
            case 'DOBJECT':
                if (!($data = mysql_fetch_assoc($this->_Resource)))
                    return false;
                else
                    return new DAL_DataObjectRow($this->_DataObject, $data);
                break;
        }
    }

    /**
     * Result Record Count
     *
     * @return int
     * @access public
     */
    function Count()
    {
        return mysql_num_rows($this->_Resource);
    }
}
?>